<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of BDb
 *
 * @author jianfeng
 */
class BAppDb {

    public function createUser($username, $email, $password, $birthday, $profile) {
        $mUser = new BMUser();
        return $mUser->register($username, $email, $password, $birthday, $profile);
    }

    public function getPersonalSpace($userId) {

        $spaces = $this->fetchUserSpaces($userId);

        foreach ($spaces as $space) {
            if ($space->getSpaceType() == BSpace::PERSONALSpaceTYPE)
                return $space;
        }

        return FALSE;
    }

    /**
     *
     * @param type $userId
     * @param type $type, include workteam_admin, workteam_member,servedteam_admin,servedteam_member
     */
    protected function fetchUserSpaces($userId, $roleType = NULL, $withRoles = FALSE) {

        $spaces = array();
        $spaceRoles = array();

        $sql = "select space_id from bln_space_application_authitem_user u, bln_application_authitem a, 
            bln_authitem_type t where 
            user_id = :UserId and u.authitem_id = a._id and a.authtype = t._id and t.typename = :AuthType";

        //get Spaces with role to user
        $sql1 = "select space_id,authitem_id from bln_space_application_authitem_user where user_id = :UserId";
        //get Spaces with role to user's circle
        $sql2 = "select space_id,authitem_id from 
            bln_space_application_circle_user u,
            bln_space_application_authitem_circle a,
            bln_space_application_circle c 
            where u.user_id = :UserId and  
            a.applicationcircle_id = u.Space_application_circle_id and 
            c._id = u.Space_application_circle_id";

        //get Spaces with role to user's circles'parent
        $sql3 = "select space_id, authitem_id from 
            bln_space_application_circle c,
            bln_space_application_circle_user u,
            bln_space_application_circle_child p,
            bln_space_application_authitem_circle a 
            where 
            u.user_id = :UserId and 
            c._id = u.Space_application_circle_id and 
            c._id = p.child_id and 
            p.parent_id = a.applicationcircle_id";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql1);

        $command->bindParam(":UserId", $userId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            array_push($spaceRoles, array($row['space_id'], $row['authitem_id']));
        }

        $command = $db->createCommand($sql2);

        $command->bindParam(":UserId", $userId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            array_push($spaceRoles, array($row['space_id'], $row['authitem_id']));
        }

        $command = $db->createCommand($sql3);

        $command->bindParam(":UserId", $userId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            array_push($spaceRoles, array($row['space_id'], $row['authitem_id']));
        }

        if ($withRoles == TRUE)
            return $spaceRoles;

        foreach ($spaceRoles as $spaceRole) {
            $spaceId = $spaceRole[0];
            $roleId = $spaceRole[1];
            if ($roleType == NULL)
                $spaces[$spaceId] = new BSpace($spaceId);
            else if ($this->isRoleTypeOf($roleId, $spaceId, $roleType))
                $spaces[$spaceId] = new BSpace($spaceId);
        }

        return $spaces;


        return FALSE;
    }

    public function getUserSpaces($userId, $type) {
        $retSpaces = array();
        $spaces = $this->fetchUserSpaces($userId, $type);

        foreach ($spaces as $id => $space) {
            if ($space->getSpaceType()->typename != BSpace::PERSONALSpaceTYPE)
                $retSpaces[$id] = $space;
        }

        return $retSpaces;
    }

    /**
     * 
     * @param type $userId
     * @return type mix
     * this function return spaceId with roleId list, such as
     * array('spaceId'=>array(...list of role id...))
     */
    public function getUserSpacesWithRoles($userId) {
        $spacesWithRoles = array();
        $spaceRoles = $this->fetchUserSpaces($userId, NULL, TRUE);

        foreach ($spaceRoles as $spaceRole) {
            $spaceId = $spaceRole[0];
            $roleId = $spaceRole[1];
            if (!isset($spacesWithRoles[$spaceId]))
                $spacesWithRoles[$spaceId] = array();
            $rolesId = $spacesWithRoles[$spaceId];
            array_push($rolesId, $roleId);
            $spacesWithRoles[$spaceId] = $rolesId;
        }
        return $spacesWithRoles;
    }

    public function getUserCircles($spaceId, $appId, $userId) {
        $circles = array();

        /*
          $sql = "select c._id from bln_spacecircle c, bln_spacecircle_user u
          where u.user_id = :UserId and c.space_id = :spaceId";
         */

        $sql = "select ac._id,ac.space_id,ac.app_id,ac.circlename,ac.profile 
            from bln_space_application_circle ac, bln_space_application_circle_user cu 
            where ac._id = cu.Space_application_circle_id and ac.space_id = :spaceId
            and cu.user_id = :UserId and ac.app_id = :appId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":UserId", $userId, PDO::PARAM_INT);
        $command->bindParam(":appId", $appId, PDO::PARAM_INT);
        $command->bindParam(":spaceId", $spaceId, PDO::PARAM_INT);

        $circleRows = $command->queryAll();

        foreach ($circleRows as $circleRow) {

            if ($circleRow['space_id'] == $spaceId && $circleRow['app_id'] == $appId) {

                $circle = new BCircle($circleRow['_id']);

                $circles[$circleRow['_id']] = $circle;

                $this->getParentCircles($circleRow['_id'], $circles);
            }
        }

        return $circles;
    }

    protected function getParentCircles($circleId, &$circles) {
        //   $circles = array();

        $sql = "select parent_id from bln_space_application_circle_child where child_id = :ChildId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":ChildId", $circleId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {

            $circleId = $row[0];

            if ($circleId != null) {
                $circles[$circleId] = new BCircle($circleId);
                $this->getParentCircle($circleId, $circles);
            }
        }
    }

    public function fetchChildAuthItems(&$childRoles, $spaceId, $authItemId) {

        $sql = "select child_id from bln_application_authitem_child where 
            parent_id = :ParentId";

        $sql2 = "select  child_authitem_id from bln_space_authitem_child where 
            parent_authitem_id = :ParentId";


        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":ParentId", $authItemId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            $childRoles[$row['child_id']] = new BAuthItem($row['child_id']);
            $this->fetchChildAuthItems($childRoles, $spaceId, $row['child_id']);
        }

        $command2 = $db->createCommand($sql2);

        $command2->bindParam(":ParentId", $authItemId, PDO::PARAM_INT);

        $dataReader2 = $command2->queryAll();

        foreach ($dataReader2 as $row) {
            $authId = $row['child_authitem_id'];
            $childRoles[$authId] = new BAuthItem($authId);
            $this->fetchChildAuthItems($childRoles, $spaceId, $authId);
        }
    }

    public function fetchParentAuthItems(&$parentRoles, $spaceId, $authItemId) {
        $sql = "select parent_id from bln_application_authitem_child where 
            child_id = :ChildId";

        $sql2 = "select  parent_authitem_id from bln_space_authitem_child where 
            child_authitem_id = :ChildId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":ChildId", $authItemId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            $parentRoles[$row['parent_id']] = new BAuthItem($row['parent_id']);
            $this->fetchParentAuthItems($parentRoles, $spaceId, $row['parent_id']);
        }

        $command2 = $db->createCommand($sql2);

        $command2->bindParam(":ChildId", $authItemId, PDO::PARAM_INT);

        $dataReader2 = $command2->queryAll();

        foreach ($dataReader2 as $row) {
            $authId = $row['parent_authitem_id'];
            $parentRoles[$authId] = new BAuthItem($authId);
            $this->fetchParentAuthItems($parentRoles, $spaceId, $authId);
        }
    }

    public function addChildRole($parentId, $childId) {
        $sql = "insert into bln_application_authitem_child(parent_id,child_id)
            values(:ParentId,:ChildId)";

        $sql2 = "select count(*) from bln_application_authitem_child where parent_id = :ParentId and child_id = :ChildId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql2);

        $command->bindParam(":ParentId", $parentId, PDO::PARAM_INT);
        $command->bindParam(":ChildId", $childId, PDO::PARAM_INT);

        $count = $command->queryScalar();

        if ($count <= 0) {
            $command = $db->createCommand($sql);
            $command->bindParam(":ParentId", $parentId, PDO::PARAM_INT);
            $command->bindParam(":ChildId", $childId, PDO::PARAM_INT);
            return $command->execute();
        }

        return $count;
    }

    public function addOperationToRole($operationId, $roleId) {
        $sql = "insert into bln_application_authitem_operation(authitem_id,operation_id)
            values(:RoleId,:OperationId)";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":RoleId", $roleId, PDO::PARAM_INT);
        $command->bindParam(":OperationId", $operationId, PDO::PARAM_INT);

        return $command->execute();
    }

    public function assignUserRole($spaceId, $userId, $roleId) {
        $sql = "insert into bln_space_application_authitem_user(space_id,authitem_id,user_id)
            values(:spaceId,:RoleId,:UserId)";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":spaceId", $spaceId, PDO::PARAM_INT);
        $command->bindParam(":RoleId", $roleId, PDO::PARAM_INT);
        $command->bindParam(":UserId", $userId, PDO::PARAM_INT);

        return $command->execute();
    }

    public function assignCircleRole($spaceId, $circleId, $roleId) {
        $sql = "insert into bln_space_application_authitem_circle(space_id,authitem_id,circle_id)
            values(:spaceId,:RoleId,:CircleId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":spaceId", $spaceId, PDO::PARAM_INT);
        $command->bindParam(":RoleId", $roleId, PDO::PARAM_INT);
        $command->bindParam(":CircleId", $circleId, PDO::PARAM_INT);

        return $command->execute();
    }

    public function addSpaceChildRole($spaceId, $parentRoleId, $childRoleId) {
        $sql = "insert into bln_space_authitem_child(space_id,parent_authitem_id,child_authitem_id)
            values(:spaceId,:ParentId,:ChildId)";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":spaceId", $spaceId, PDO::PARAM_INT);
        $command->bindParam(":ParentId", $parentId, PDO::PARAM_INT);
        $command->bindParam(":ChildId", $childId, PDO::PARAM_INT);

        return $command->execute();
    }

    public function getSpaceTypeId($typeName) {
        return BMSpaceType::model()->find("typename=:typeName", array(":typeName" => $typeName))->_id;
    }

    public function getSpaces($condition) {
        $spaces = array();

        if ($condition == BSpace::SpaceType_AllTypesExceptPersonal)
            $mSpaces = BMSpace::model()->findAllBySql(
                    "select s._id as _id from bln_space s,bln_spacetype t,bln_space_Spacetype st where
                        s._id = st.space_id and t._id = st.Spacetype_id and t.typename != :TypeName", array(":TypeName" => BSpace::PERSONALSpaceTYPE));
        else
            $mSpaces = BMSpace::model()->findAll();

        foreach ($mSpaces as $mSpace) {
            array_push($spaces, new BSpace($mSpace->_id));
        }

        return $spaces;
    }

    public function getspaceIdByName($spaceName) {
        $space = BMSpace::model()->findBySql("select * from bln_space where Spacename=:SpaceName", array(':SpaceName' => $spaceName));

        return $space->_id;
    }

    public function createSpace($spaceName, $spaceType = BSpace::NORMALSpaceTYPE, $spaceCategory = BSpace::UNKNOWNCATEGORY) {

        $typeId = null;
        $categoryId = null;

        $conn = Yii::app()->db;

        $transaction = $conn->beginTransaction();
        try {

            $insertSpaceSql = "insert into bln_space(Spacename) values(:SpaceName)";
            $insertSpaceTypeSql = "insert into bln_space_Spacetype(space_id,Spacetype_id) values(:spaceId,:SpaceTypeId)";
            $insertSpaceCategorySql = "insert into bln_space_Spacecategory(space_id,Spacecategory_id) values(:spaceId,:SpaceCategoryId)";

            //insert Space
            $command1 = $conn->createCommand($insertSpaceSql);
            $command1->bindParam(":SpaceName", $spaceName, PDO::PARAM_STR);

            $command1->execute();

            $sql = 'select _id from bln_space where Spacename = :SpaceName';

            $newspaceId = $conn->createCommand($sql)->queryScalar(array(':SpaceName' => $spaceName));

            //insert Spacetype

            $sql = "select _id from bln_spacetype where typename = :SpaceType";

            if (is_string($spaceType))
                $typeId = $conn->createCommand($sql)->queryScalar(array(':SpaceType' => $spaceType));

            if (is_numeric($spaceType)) {
                $typeId = $spaceType;
            }
            $command2 = $conn->createCommand($insertSpaceTypeSql);

            $command2->bindParam(":spaceId", $newspaceId, PDO::PARAM_INT);
            $command2->bindParam(":SpaceTypeId", $typeId, PDO::PARAM_INT);

            $command2->execute();

            //insert Spacecategory

            $sql = "select _id from bln_spacecategory where categoryname = :SpaceCategory";

            if (is_string($spaceCategory))
                $categoryId = $conn->createCommand($sql)->queryScalar(array(':SpaceCategory' => $spaceCategory));

            if (is_numeric($spaceCategory))
                $categoryId = $spaceCategory;

            $command3 = $conn->createCommand($insertSpaceCategorySql);

            $command3->bindParam(":spaceId", $newspaceId, PDO::PARAM_INT);
            $command3->bindParam(":SpaceCategoryId", $categoryId, PDO::PARAM_INT);

            $command3->execute();

            //.... other SQL executions
            $transaction->commit();
        } catch (Exception $e) {
            $transaction->rollBack();
            return false;
        }

        return $newspaceId;
    }

    public function getSpaceType($spaceId) {
        $sql = "select typename from bln_spacetype t,bln_space_Spacetype s
             where t._id = s.Spacetype_id and s.space_id = :spaceId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":spaceId", $spaceId, PDO::PARAM_INT);

        $typename = $command->queryScalar();

        return $typename;
    }

    public function getApps($spaceId) {
        $apps = array();

        $sql = "select app_id from bln_space_application where space_id = :spaceId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":spaceId", $spaceId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            $appClass = $this->getAppClass($row[0]);
            $apps[$row[0]] = new $appClass;
        }

        return $apps;
    }

    public function getAppRoles($appId) {
        $sql = "select * from bln_application_authitem where app_id = :appId";

        $roles = array();

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":appId", $appId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            $roles[$row[0]] = new BAuthItem($row[0]);
        }

        return $roles;
    }

    public function getUserRoles($userId, $spaceId, $appId) {

        $sql = "select * from bln_space_application_authitem_user a, bln_application_authitem b
            where a.authitem_id = b._id and b.app_id = :appId
             and a.space_id = :spaceId and a.user_id = :UserId";

        $roles = array();

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":spaceId", $spaceId, PDO::PARAM_INT);
        $command->bindParam(":UserId", $userId, PDO::PARAM_INT);
        $command->bindParam(":appId", $appId, PDO::PARAM_INT);

        //first get roles in specific app with appId
        $dataReader = $command->queryAll();
        foreach ($dataReader as $row) {
            $roles[$row['authitem_id']] = new BAuthItem($row['authitem_id']);
        }

        //then get user's Space role from SpaceEngine
        $appId = 1;
        $dataReader = $command->queryAll();
        foreach ($dataReader as $row) {
            $roles[$row['authitem_id']] = new BAuthItem($row['authitem_id']);
        }

        //get roles by with circle
        $circles = $this->getUserCircles($spaceId, $appId, $userId);

        foreach ($circles as $circle) {
            array_merge($roles, $this->getCircleRoles($circle->getId(), $spaceId, $appId));
        }

        return $roles;
    }

    public function getCircleRoles($circleId, $spaceId, $appId) {
        $sql = "select a._id from bln_space_application_authitem_circle a, bln_space_application_circle c 
             where c.app_id = :appId  and c.space_id = :spaceId and c._id = a.applicationcircle_id 
             and a.applicationcircle_id = :CircleId";

        $roles = array();

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":appId", $appId, PDO::PARAM_INT);
        $command->bindParam(":spaceId", $spaceId, PDO::PARAM_INT);
        $command->bindParam(":CircleId", $circleId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            $roles[$row['authitem_id']] = new BAuthItem($row['authitem_id']);
        }

        return $roles;
    }

    public function getOperations($roleId) {
        $operations = array();

        $sql = "select * from bln_application_authitem_operation where 
             authitem_id = :RoleId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":RoleId", $roleId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            $operations[$row[2]] = new BOperation($row[2]);
        }

        return $operations;
    }

    public function getRoleTypeId($typeName) {
        $sql = "select _id from bln_authitem_type where 
             typename = :TypeName";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":TypeName", $typeName, PDO::PARAM_STR);

        return $command->queryScalar();
    }

    public function isRoleTypeOf($roleId, $spaceId, $typeName) {
        $sql = "select typename from bln_application_authitem a, 
            bln_authitem_type t
            where 
            a.authtype = t._id and 
            a._id = :RoleId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":RoleId", $roleId, PDO::PARAM_INT);

        $roleType = $command->queryScalar();

        if ($roleType !== buser::SpaceMIXEDROLE && $roleType !== $typeName)
            return FALSE;

        if ($roleType == buser::SpaceMIXEDROLE) {
            $childRoles = array();
            $this->fetchChildAuthItems($childRoles, $spaceId, $roleId);

            foreach ($childRoles as $role) {
                if ($role->getAuthType() == $typeName)
                    return true;
                else
                    $this->isRoleTypeOf($role->getId(), $spaceId, $typeName);
            }

            return FALSE;
        }

        return TRUE;
    }

    public function getRoleType($roleId) {
        
    }

    public function createRole($appId, $name, $type, $showname) {


        $mRole = new BMApplicationAuthItem;

        $typeId = $this->getRoleTypeId($type);

        $mRole->authname = $name;
        $mRole->authtype = $typeId;
        $mRole->app_id = $appId;
        $mRole->showname = $showname;

        $mRole->save();

        return $mRole->_id;
    }

    public function addRole($appId, $name, $type, $showname) {
        $count = -1;

        $sql = "select a._id from bln_application_authitem a,bln_authitem_type b 
            where app_id = :appId and authname = :RoleName 
            and typename = :AuthType and a.authtype = b._id";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":appId", $appId, PDO::PARAM_INT);
        $command->bindParam(":RoleName", $name, PDO::PARAM_STR);
        $command->bindParam(":AuthType", $type, PDO::PARAM_STR);

        $count = $command->queryScalar();

        if ($count != NULL && $count > 0) {
            return $count;
        } else {
            $this->createRole($appId, $name, $type, $showname);
        }
    }

    public function getSpaceScopeTypeId($scopename) {
        $sql = "select _id from bln_space_scopetype where scopename = :ScopeName";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":ScopeName", $scopename, PDO::PARAM_STR);

        return $command->queryScalar();
    }

    public function getVisibleApps($space = null, $spaceType = null, $spaceCategory = null) {

        $apps = array();

        $sql = "select _id from bln_application";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $rows = $command->query();

        foreach ($rows as $row) {

            $app = BApp::getInstanceById($row['_id']);

            if ($app->isVisible($space, $spaceType, $spaceCategory))
                $apps[$row['_id']] = $app;
        }

        return $apps;
    }

    public function addContextVariable($name, $value, $scope, $type = NULL) {

        if ($type == NULL)
            $type = 'string';

        $db = Yii::app()->getDb();

        $findSql = "select * from bln_Transit_variable where name = :Name and scope = :Scope and vtype = :Type";

        $command = $db->createCommand($findSql);

        $command->bindParam(":Name", $name, PDO::PARAM_STR);
        $command->bindParam(":Scope", $scope, PDO::PARAM_STR);
        $command->bindParam(":Type", $type, PDO::PARAM_STR);

        $rows = $command->queryRow();

        //find row, update, otherwise insert
        if (!$rows) {
            $sql = "insert bln_Transit_variable(name,value,scope,vtype) 
            values(:Name,:Value,:Scope,:Type)";

            $command = $db->createCommand($sql);

            $command->bindParam(":Name", $name, PDO::PARAM_STR);
            $command->bindParam(":Value", $value, PDO::PARAM_STR);
            $command->bindParam(":Scope", $scope, PDO::PARAM_STR);
            $command->bindParam(":Type", $type, PDO::PARAM_STR);

            return $command->execute();
        } else {
            $sql = "update bln_Transit_variable set value = :Value where name = :Name and scope = :Scope and vtype = :Type";
            $command = $db->createCommand($sql);

            $command->bindParam(":Name", $name, PDO::PARAM_STR);
            $command->bindParam(":Value", $value, PDO::PARAM_STR);
            $command->bindParam(":Scope", $scope, PDO::PARAM_STR);
            $command->bindParam(":Type", $type, PDO::PARAM_STR);

            return $command->execute();
        }
    }

    public function getContextVariable($name, $scope, $type = NULL) {

        if ($type == NULL)
            $type = 'string';

        $sql = "select value from bln_Transit_variable where name = :Name and scope = :Scope and vtype = :Type";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":Name", $name, PDO::PARAM_STR);
        $command->bindParam(":Scope", $scope, PDO::PARAM_STR);
        $command->bindParam(":Type", $type, PDO::PARAM_STR);

        return $command->queryScalar();
    }

    public function getContextVariablesByScope($scope, $type = NULL) {

        $sql = "select name,value from bln_Transit_variable where scope = :Scope and vtype = :Type";

        if ($type == NULL)
            $sql = "select name,value from bln_Transit_variable where scope = :Scope";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":Scope", $scope, PDO::PARAM_STR);

        if ($type != NULL)
            $command->bindParam(":Type", $type, PDO::PARAM_STR);

        $rows = $command->query();

        $variables = array();

        foreach ($rows as $row) {
            $variables[$row['name']] = $row['value'];
        }

        return $variables;
    }

    public function deleteContextVariable($scope, $name = NULL, $type = NULL) {
        $sql = "delete from bln_Transit_variable where scope = :Scope";

        if ($name != NULL)
            $sql = "delete from bln_Transit_variable where scope = :Scope and name = :Name";

        if ($type != NULL)
            $sql = "delete from bln_Transit_variable where scope = :Scope and vtype = :Type";

        if ($name != NULL && $type != NULL)
            $sql = "delete from bln_Transit_variable where scope = :Scope and vtype = :Type and name = :Name";


        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        if ($name != NULL)
            $command->bindParam(":Name", $name, PDO::PARAM_STR);

        if ($type != NULL)
            $command->bindParam(":Type", $type, PDO::PARAM_STR);

        if ($type != NULL && $name != NULL) {
            $command->bindParam(":Type", $type, PDO::PARAM_STR);
            $command->bindParam(":Name", $name, PDO::PARAM_STR);
        }

        $command->bindParam(":Scope", $scope, PDO::PARAM_STR);

        return $command->execute();
    }

    public function addOperationRequest($data) {
        $receiver = $data['receiver'];
        $sender = $data['sender'];
        $operation = $data['operation'];
        $operationData = serialize($data['data']);
        $status = $data['status'];

        $sql = "insert bln_operation_request(
            operationname,operationdata,status,start_time,alive_length,
            to_user,to_Space,to_Space_app,
            from_user,from_Space,from_Space_app)
            values(
            :OperationName,:OperationData,:Status,:StartTime,:AliveLength,
            :ToUser,:ToSpace,:ToApp,
            :FromUser,:FromSpace,:FromApp)";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":OperationName", $operation['name'], PDO::PARAM_STR);
        $command->bindParam(":OperationData", $operationData, PDO::PARAM_STR);
        $command->bindParam(":Status", $status['status'], PDO::PARAM_STR);
        $command->bindParam(":StartTime", $status['startTime'], PDO::PARAM_INT);
        $command->bindParam(":AliveLength", $status['aliveLength'], PDO::PARAM_INT);
        $command->bindParam(":ToUser", $receiver['userId'], PDO::PARAM_INT);
        $command->bindParam(":ToSpace", $receiver['spaceId'], PDO::PARAM_INT);
        $command->bindParam(":ToApp", $receiver['appId'], PDO::PARAM_INT);
        $command->bindParam(":FromUser", $sender['userId'], PDO::PARAM_INT);
        $command->bindParam(":FromSpace", $sender['spaceId'], PDO::PARAM_INT);
        $command->bindParam(":FromApp", $sender['appId'], PDO::PARAM_INT);

        if ($command->execute() > 0)
            return $this->getOperationRequestIdByStartTime($operation['name'], $status['startTime']);
        return FALSE;
    }

    public function getOperationRequestIdByStartTime($operationName, $startTime) {
        $sql = "select * from bln_operation_request where start_time = :StartTime and operationname = :OperationName";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":OperationName", $operationName, PDO::PARAM_STR);
        $command->bindParam(":StartTime", $startTime, PDO::PARAM_INT);

        $requestId = $command->queryScalar();
        return $requestId;
    }

    public function addMessageRequest($data) {
        $receiver = $data['receiver'];
        $sender = $data['sender'];
        $operation = $data['operation'];
        $operationData = serialize($data['data']);
        $status = $data['status'];

        $sql = "insert bln_message_request(
            operationname,operationdata,status,start_time,alive_length,
            to_user,to_Space,to_Space_app,
            from_user,from_Space,from_Space_app)
            values(
            :OperationName,:OperationData,:Status,:StartTime,:AliveLength,
            :ToUser,:ToSpace,:ToApp,
            :FromUser,:FromSpace,:FromApp)";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":OperationName", $operation['name'], PDO::PARAM_STR);
        $command->bindParam(":OperationData", $operationData, PDO::PARAM_STR);
        $command->bindParam(":Status", $status['status'], PDO::PARAM_STR);
        $command->bindParam(":StartTime", $status['startTime'], PDO::PARAM_INT);
        $command->bindParam(":AliveLength", $status['aliveLength'], PDO::PARAM_INT);
        $command->bindParam(":ToUser", $receiver['userId'], PDO::PARAM_INT);
        $command->bindParam(":ToSpace", $receiver['spaceId'], PDO::PARAM_INT);
        $command->bindParam(":ToApp", $receiver['appId'], PDO::PARAM_INT);
        $command->bindParam(":FromUser", $sender['userId'], PDO::PARAM_INT);
        $command->bindParam(":FromSpace", $sender['spaceId'], PDO::PARAM_INT);
        $command->bindParam(":FromApp", $sender['appId'], PDO::PARAM_INT);

        return $command->execute();
    }

    public function getUserIdByLoginId($email) {
        $sql = "select _id from bln_user where email = :Email";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":Email", $email, PDO::PARAM_STR);

        return $command->queryScalar();
    }

    public function getUserById($userId) {
        $sql = "select * from bln_user where _id = :UserId";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":UserId", $userId, PDO::PARAM_INT);

        return $command->queryRow();
    }

    public function searchSpace($spaceName, $spaceType, $spaceCategory) {
        if ($spaceCategory != NULL)
            $sql = "select s._id from bln_space s,bln_space_Spacetype st, bln_serive_Spacecategory sc,bln_spacetype t 
                bln_spacecategory c where 
                s_id = st.space_id and s._id = sc.space_id 
                and st.Spacetype_id = t._id and sc.Spacecategory_id = c._id 
                and s.Spacename = :SpaceName and t.typename = :SpaceType and c.categoryname = :SpaceCategory";
        else {
            $sql = "select s._id from bln_space s,bln_space_Spacetype st, bln_spacetype t 
                where 
                s_id = st.space_id  and st.Spacetype_id = t._id  
                and s.Spacename = :SpaceName and t.typename = :SpaceType";
        }

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":SpaceName", $spaceName, PDO::PARAM_STR);
        $command->bindParam(":SpaceType", $spaceType, PDO::PARAM_STR);

        if ($spaceCategory != NULL)
            $command->bindParam(":SpaceCategory", $spaceCategory, PDO::PARAM_STR);

        return $command->queryScalar();
    }

    public function getappId($classname) {

        $app = null;

        $sql = "select * from bln_application where classname = :ClassName";

        $app = BMApplication::model()->findBySql($sql, array(":ClassName" => $classname));

        if ($app == null)
            return FALSE;
        else {
            $id = $app->_id;
            return $id;
        }
    }

    public function getOperationPermits($spaceId, $appId, $operationId) {
        $sql = "select ao.authitem_id as authitem_id from bln_application_authitem_operation ao, bln_application_authitem aa, bln_space_application sa where
            ao.authitem_id = aa._id and aa.app_id = sa.app_id 
            and sa.space_id = :spaceId and aa.app_id = :appId and ao.operation_id = :OperationId";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":spaceId", $spaceId, PDO::PARAM_INT);
        $command->bindParam(":appId", $appId, PDO::PARAM_INT);
        $command->bindParam(":appId", $operationId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        $items = array();

        foreach ($dataReader as $row) {
            array_push($items, $row['authitem_id']);
        }

        return $items;
    }

    public function getOperationId($appId, $functionName = NULL, $operationName = NULL) {

        if ($functionName != NULL)
            $sql = "select _id from bln_application_operation where app_id = :appId and functionname=:FunctionName";

        if ($functionName == NULL && $operationName != NULL)
            $sql = "select _id from bln_application_operation where app_id = :appId and operationname=:OperationName";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":appId", $appId, PDO::PARAM_INT);

        if ($functionName != NULL)
            $command->bindParam(":FunctionName", $functionName, PDO::PARAM_STR);

        if ($functionName == NULL && $operationName != NULL)
            $command->bindParam(":OperationName", $operationName, PDO::PARAM_STR);

        return $command->queryScalar();
    }

}

?>
